package es.udc.emarketapp.emarket.model.orderLine.dao;

import java.util.List;
import java.util.ArrayList;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import es.udc.emarketapp.emarket.model.orderLine.vo.OrderLineVO;
import es.udc.fbellas.j2ee.util.exceptions.InstanceNotFoundException;
import es.udc.fbellas.j2ee.util.exceptions.InternalErrorException;
import es.udc.fbellas.j2ee.util.sql.GeneralOperations;

public abstract class AbstractSQLOrderLineDAO implements SQLOrderLineDAO {

	protected AbstractSQLOrderLineDAO() {
	}

	
	public boolean exists(Connection connection, Long nLinea,
			Long nPedido, Long nCompra)
			throws InternalErrorException {

		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;

		try {

			// Create "preparedStatement".
			String queryString = "SELECT n_linea FROM linea_pedido "
					+ "WHERE n_linea = ? AND n_pedido = ? AND n_compra = ?";
			preparedStatement = connection.prepareStatement(queryString);

			// Fill "preparedStatement".
			preparedStatement.setLong(1, nLinea.longValue());
			preparedStatement.setLong(2, nPedido.longValue());
			preparedStatement.setLong(3, nCompra.longValue());

			// Execute query.
			resultSet = preparedStatement.executeQuery();

			return resultSet.next();

		} catch (SQLException e) {
			throw new InternalErrorException(e);
		} finally {
			GeneralOperations.closeResultSet(resultSet);
			GeneralOperations.closeStatement(preparedStatement);
		}

	}// exists

	public OrderLineVO findById(Connection connection, Long nLinea, 
			Long nPedido, Long nCompra)
			throws InstanceNotFoundException, InternalErrorException {

		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;

		try {

			// Create "preparedStatement".
			String queryString = "SELECT id_prod, uds, valor, "
					+ "peso FROM linea_pedido WHERE n_linea = ? "
					+ "AND n_pedido = ? AND n_compra = ? ";
			preparedStatement = connection.prepareStatement(queryString);

			// Fill "preparedStatement".
			preparedStatement.setLong(1, nLinea.longValue());
			preparedStatement.setLong(2, nPedido.longValue());
			preparedStatement.setLong(3, nCompra.longValue());

			// Execute query.
			resultSet = preparedStatement.executeQuery();

			if (!resultSet.next()) {
				throw new InstanceNotFoundException(nLinea,
						OrderLineVO.class.getName());
			}

			// Get results.
			int i = 1;
			Long idProd = resultSet.getLong(i++);
			Long uds = resultSet.getLong(i++);
			double valor = resultSet.getDouble(i++);
			double peso = resultSet.getDouble(i++);

			// Return the value object.
			return new OrderLineVO(nLinea, nPedido, nCompra, idProd,
					valor, peso);
			
		} catch (SQLException e) {
			throw new InternalErrorException(e);
		} finally {
			GeneralOperations.closeResultSet(resultSet);
			GeneralOperations.closeStatement(preparedStatement);
		}

	}// findById

	public List<OrderLineVO> findByOrder(Connection connection, Long nPedido, 
			Long nCompra, int startIndex, int count)
			throws InternalErrorException {

		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;

		try {

			// Create "preparedStatement".
			String queryString = "SELECT n_linea, id_prod, "
				    + "uds, valor, peso FROM linea_pedido "
				    + "WHERE n_pedido = ? AND n_compra = ? ";

			preparedStatement = connection.prepareStatement(queryString,
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);

			preparedStatement.setLong(1, nPedido);
			preparedStatement.setLong(2, nCompra);

			// Execute query.
			resultSet = preparedStatement.executeQuery();

			// Read objects.
			List<OrderLineVO> orderLineVOs = new ArrayList<OrderLineVO>();
			int currentCount = 0;

			if ((startIndex >= 1) && resultSet.absolute(startIndex)) {
				do {
					int i = 1;
					Long nLinea = resultSet.getLong(i++);
					Long idProd = resultSet.getLong(i++);
					Long uds = resultSet.getLong(i++);
					double valor = resultSet.getDouble(i++);
					double peso = resultSet.getDouble(i++);
					
					orderLineVOs.add(new OrderLineVO(nLinea, nPedido, nCompra,
							idProd, uds, valor, peso));

					currentCount++;

				} while (resultSet.next() && (currentCount < count));
			}// if

			// Return value objects.
			return orderLineVOs;

		} catch (SQLException e) {
			throw new InternalErrorException(e);
		} finally {
			GeneralOperations.closeResultSet(resultSet);
			GeneralOperations.closeStatement(preparedStatement);
		}

	}// findByOrder

	public List<OrderLineVO> findAll(Connection connection, int startIndex,
			int count) throws InternalErrorException {

		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;

		try {

			// Create "preparedStatement".
			String queryString = "SELECT n_linea, n_pedido, n_compra, "
					+ "id_prod, uds, valor, peso FROM linea_pedido";

			preparedStatement = connection.prepareStatement(queryString,
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);

			// Execute query.
			resultSet = preparedStatement.executeQuery();

			// Read objects.
			List<OrderLineVO> orderLineVOs = new ArrayList<OrderLineVO>();
			int currentCount = 0;

			if ((startIndex >= 1) && resultSet.absolute(startIndex)) {
				do {
					int i = 1;
					Long nLinea = resultSet.getLong(i++);
					Long nPedido = resultSet.getLong(i++);
					Long nCompra = resultSet.getLong(i++);
					Long idProd = resultSet.getLong(i++);
					Long uds = resultSet.getLong(i++);
					double valor = resultSet.getDouble(i++);
					double peso = resultSet.getDouble(i++);
					
					orderLineVOs.add(new OrderLineVO(nLinea, nPedido, 
							nCompra, idProd, uds, valor, peso));

					currentCount++;

				} while (resultSet.next() && (currentCount < count));
			}// if

			// Return value objects.
			return orderLineVOs;

		} catch (SQLException e) {
			throw new InternalErrorException(e);
		} finally {
			GeneralOperations.closeResultSet(resultSet);
			GeneralOperations.closeStatement(preparedStatement);
		}

	}// findAll

        public int countUdsByOrder(Connection connection, Long nPedido)
			throws InternalErrorException, InstanceNotFoundException {

		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;

		try {

			// Create "preparedStatement".
			String queryString = "SELECT SUM(uds) FROM linea_pedido "
                                + "where n_pedido = ?";
			preparedStatement = connection.prepareStatement(queryString);

			// Fill "preparedStatement".
			preparedStatement.setLong(1, nPedido.longValue());

			// Execute query.
			resultSet = preparedStatement.executeQuery();

		        if (!resultSet.next()) {
				throw new InstanceNotFoundException(nPedido,
						OrderLineVO.class.getName());
			}

			// Get results
			int nUds = resultSet.getInt(1);


			// Return the value object.
			return nUds;

		} catch (SQLException e) {
			throw new InternalErrorException(e);
		} finally {
			GeneralOperations.closeResultSet(resultSet);
			GeneralOperations.closeStatement(preparedStatement);
		}

	}// countUdsByOrder

	public void update(Connection connection, OrderLineVO orderLineVO)
			throws InstanceNotFoundException, InternalErrorException {
		PreparedStatement preparedStatement = null;

		try {

			// Create "preparedStatement".
			String queryString = "UPDATE linea_pedido SET id_prod = ?, "
					+ "uds = ?, valor = ?, peso = ? WHERE n_linea = ? "
					+ "AND n_pedido = ? AND n_compra = ?";
			
			preparedStatement = connection.prepareStatement(queryString);

			// Fill "preparedStatement".
			int i = 1;
            preparedStatement.setLong(i++, orderLineVO.getIdProd());
            preparedStatement.setLong(i++, orderLineVO.getUds());
            preparedStatement.setDouble(i++, orderLineVO.getValor());
            preparedStatement.setDouble(i++, orderLineVO.getPeso());
            preparedStatement.setLong(i++, orderLineVO.getnLinea());
            preparedStatement.setLong(i++, orderLineVO.getnPedido());
            preparedStatement.setLong(i++, orderLineVO.getnCompra());
			
	
			// Execute query.
			int updatedRows = preparedStatement.executeUpdate();

			if (updatedRows == 0) {
				throw new InstanceNotFoundException(orderLineVO.getnLinea()
						.longValue(), OrderLineVO.class.getName());
			}

			if (updatedRows > 1) {
				throw new SQLException("Fila duplicada para el n_linea = '"
						+ orderLineVO.getnLinea().longValue()
						+ "' y n_pedido = '"
						+ orderLineVO.getnPedido().longValue()
						+ "' y n_compra = '" 
						+ orderLineVO.getnCompra().longValue()
						+ "' en la tabla 'Liena_pedido'");
			}

		} catch (SQLException e) {
			throw new InternalErrorException(e);
		} finally {
			GeneralOperations.closeStatement(preparedStatement);
		}

	}// update

	public void remove(Connection connection, Long nLinea, 
			Long nPedido, Long nCompra)
			throws InstanceNotFoundException, InternalErrorException {
		PreparedStatement preparedStatement = null;

		try {

			// Create "preparedStatement".
			String queryString = "DELETE FROM linea_pedido "
						+ "WHERE n_linea = ? AND n_pedido = ? "
						+ "AND n_compra = ?";
			preparedStatement = connection.prepareStatement(queryString);

			// Fill "preparedStatement".
			preparedStatement.setLong(1, nLinea.longValue());
			preparedStatement.setLong(2, nPedido.longValue());
			preparedStatement.setLong(3, nCompra.longValue());

			// Execute query.
			int removedRows = preparedStatement.executeUpdate();

			if (removedRows == 0) {
				throw new InstanceNotFoundException(nLinea.longValue(),
						OrderLineVO.class.getName());
			}

		} catch (SQLException e) {
			throw new InternalErrorException(e);
		} finally {
			GeneralOperations.closeStatement(preparedStatement);
		}

	}// remove

}
